USE [Finances]
GO
/****** Object:  View [dbo].[accountsView]    Script Date: 09/15/2008 22:26:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[accountsView]
AS
SELECT
		T.account_id as account_id,
		ISNULL(TL.amount, 0.00) as ledger, -- Amount after both reconciled and non-reconciled
		ISNULL(TA.amount, 0.00) as available -- Amount after only reconciled (right now balance)

	FROM
		Transactions T
	LEFT JOIN
	(
		SELECT
			T.account_id as account_id,
			SUM(T.amount) as amount
		FROM
			Transactions T
		INNER JOIN
			Transactions_Status TS ON T.transactions_status_id = TS.transactions_status_id
		GROUP BY
			T.account_id
	) TL ON T.account_id = TL.account_id
	
	LEFT JOIN
	(
		SELECT
			T.account_id as account_id,
			SUM(T.amount) as amount
		FROM
			Transactions T
		INNER JOIN
			Transactions_Status TS on T.transactions_status_id = TS.transactions_status_id
		WHERE
			TS.modifies_balance = 1
		GROUP BY
			T.account_id
	) TA ON T.account_id = TA.account_id

	GROUP BY
		T.account_id, TL.amount, TA.amount